"""
Case Type   : 兼容M*数据库subdate函数
Case Name   : SUBDATE(text date, INTERVAL expr unit)函数，第一个参数为datetime类型，
              参数2测试
Create At   : 2023.04.07
Owner       : z@wan005
Description :
    1、interval单位为year
    2、interval单位为month
    3、interval单位为day
    4、interval单位为hour
    5、interval单位为minute
    6、interval单位为second
    7、interval的时间单位为day to hour
    8、interval的时间单位为day to minute
    9、interval的时间单位为day to second
    10、interval的时间单位为hour to minute
    11、interval的时间单位为hour to second
    12、interval的时间单位为minute to second
    13、清理环境
Expect      :
    1、成功
    2、成功
    3、成功
    4、成功
    5、成功
    6、成功
    7、成功
    8、成功
    9、成功
    10、成功
    11、成功
    12、成功
    13、成功
History     :modified by zhanghuan96，2024.06.28，优化断言
             modified by zhanghuan，2024.8.2，适配最新代码，修改断言
             modified by zhanghuan96，2024.8.26，IAHP3M问题修复，代码变更
"""

import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class CompatibilityTest022(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.common = Common()
        self.sh_primary = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.db_name = "db_subdate_0022"

    def test_subdate(self):
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.log.info('----前置条件：创建兼容b库---')
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"create database {self.db_name} dbcompatibility 'b';"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, sql_res)

        text = '--step1:interval单位为year;expect:成功--'
        self.log.info(text)
        sql_cmd = "select subdate('2022-01-01 01:01:01', interval 1 year);" \
                  "select subdate('2022-01-01 01:01:01', interval 9.5 year);" \
                  "select subdate('2022-01-01 01:01:01', interval 22.4 year);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertTrue(len(sql_res.strip().splitlines()) >= 12, '行数不到14行')
        self.assertEqual('2021-01-01 01:01:01',
                         sql_res.splitlines()[2].strip(), '执行失败' + text)
        self.assertEqual('2012-01-01 01:01:01',
                         sql_res.splitlines()[7].strip(), '执行失败' + text)
        self.assertEqual('2000-01-01 01:01:01',
                         sql_res.splitlines()[-2].strip(), '执行失败' + text)

        text = '--step2:interval单位为month;expect:成功--'
        self.log.info(text)
        sql_cmd = "select subdate('2022-01-01 01:01:01', interval 1 month);" \
                  "select " \
                  "subdate('2022-01-01 01:01:01', interval 1.5 month);" \
                  "select " \
                  "subdate('2022-01-01 01:01:01', interval 1.444 month);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertTrue(len(sql_res.strip().splitlines()) >= 14, '行数不到14行')
        self.assertEqual('2021-12-01 01:01:01',
                         sql_res.splitlines()[2].strip(), '执行失败' + text)
        self.assertEqual('2021-11-01 01:01:01',
                         sql_res.splitlines()[7].strip(), '执行失败' + text)
        self.assertEqual('2021-12-01 01:01:01',
                         sql_res.splitlines()[-2].strip(), '执行失败' + text)

        text = '--step3:interval的时间单位为day;expect:成功--'
        self.log.info(text)
        sql_cmd = "select subdate('2022-01-01 01:01:01', interval 1 day);" \
                  "select " \
                  "subdate('2022-01-01 01:01:01', interval 1.999 day);" \
                  "select subdate('2022-01-01 01:01:01', interval 1.4 day);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertTrue(len(sql_res.strip().splitlines()) >= 14, '行数不到14行')
        self.assertEqual('2021-12-31 01:01:01',
                         sql_res.splitlines()[2].strip(), '执行失败' + text)
        self.assertEqual('2021-12-30 01:01:01',
                         sql_res.splitlines()[7].strip(), '执行失败' + text)
        self.assertEqual('2021-12-31 01:01:01',
                         sql_res.splitlines()[-2].strip(), '执行失败' + text)

        text = '--step4:interval的时间单位为hour;expect:成功--'
        self.log.info(text)
        sql_cmd = "select subdate('2022-01-01 01:01:01', interval 1 hour);" \
                  "select subdate('2022-01-01 23:59:59', interval 1 hour);" \
                  "select " \
                  "subdate('2022-01-01 23:59:59', interval 1.999 hour);" \
                  "select subdate" \
                  "('2022-01-01 23:59:59', interval 1.444444 hour);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertTrue(len(sql_res.strip().splitlines()) >= 19, '行数不到19行')
        self.assertEqual('2022-01-01 00:01:01',
                         sql_res.splitlines()[2].strip(), '执行失败' + text)
        self.assertEqual('2022-01-01 22:59:59',
                         sql_res.splitlines()[7].strip(), '执行失败' + text)
        self.assertEqual('2022-01-01 21:59:59',
                         sql_res.splitlines()[12].strip(), '执行失败' + text)
        self.assertEqual('2022-01-01 22:59:59',
                         sql_res.splitlines()[-2].strip(), '执行失败' + text)

        text = '--step5:interval的时间单位为minute;expect:成功--'
        self.log.info(text)
        sql_cmd = "select " \
                  "subdate('2022-01-01 23:59:59', interval 59 minute);" \
                  "select " \
                  "subdate('2022-01-01 23:59:59', interval 59.9 minute);" \
                  "select " \
                  "subdate('2022-01-01 23:59:59', interval 59.444 minute);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertTrue(len(sql_res.strip().splitlines()) >= 14, '行数不到14行')
        self.assertEqual('2022-01-01 23:00:59',
                         sql_res.splitlines()[2].strip(), '执行失败' + text)
        self.assertEqual('2022-01-01 22:59:59',
                         sql_res.splitlines()[7].strip(), '执行失败' + text)
        self.assertEqual('2022-01-01 23:00:59',
                         sql_res.splitlines()[-2].strip(), '执行失败' + text)

        text = '--step6:interval的时间单位为second;expect:成功--'
        self.log.info(text)
        sql_cmd = "select " \
                  "subdate('2022-01-01 23:59:59', interval 59 second);" \
                  "select " \
                  "subdate('2022-01-01 23:59:59', interval 59.999 second);" \
                  "select " \
                  "subdate('2022-01-01 23:59:59', interval 59.4445 second);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertTrue(len(sql_res.strip().splitlines()) >= 14, '行数不到14行')
        self.assertEqual('2022-01-01 23:59:00',
                         sql_res.splitlines()[2].strip(), '执行失败' + text)
        self.assertEqual('2022-01-01 23:58:59.001',
                         sql_res.splitlines()[7].strip(), '执行失败' + text)
        self.assertEqual('2022-01-01 23:58:59.5555',
                         sql_res.splitlines()[-2].strip(), '执行失败' + text)

        text = '--step7:interval的时间单位为day to hour;expect:成功--'
        self.log.info(text)
        sql_cmd = "set dolphin.support_interval_to =on;" \
                   "select " \
                  "subdate('0002-1-1 23:59:59', interval 1 day to hour);" \
                  "select " \
                  "subdate('0002-1-1 23:59:59.9', interval 1 day to hour);" \
                  "select subdate" \
                  "('0002-1-1 23:59:59.9999999', interval 1 day to hour);" \
                  "select subdate" \
                  "('0002-1-1 23:59:59', interval 1.5 day to hour);" \
                  "select " \
                  "subdate('0002-1-1 23:59:59', interval 1.4 day to hour) ;"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.assertTrue(len(sql_res.strip().splitlines()) >= 24, '行数不到24行')
        self.log.info(sql_res)
        self.assertEqual('0002-01-01 22:59:59',
                         sql_res.splitlines()[3].strip(), '执行失败' + text)
        self.assertEqual('0002-01-01 22:59:59.9',
                         sql_res.splitlines()[8].strip(), '执行失败' + text)
        self.assertEqual('0002-01-01 23:00:00',
                         sql_res.splitlines()[13].strip(), '执行失败' + text)
        self.assertEqual('0001-12-31 18:59:59',
                         sql_res.splitlines()[18].strip(), '执行失败' + text)
        self.assertEqual('0001-12-31 19:59:59',
                         sql_res.splitlines()[-2].strip(), '执行失败' + text)

        text = '--step8:interval的时间单位为day to minute;expect:成功--'
        self.log.info(text)
        sql_cmd = "set dolphin.support_interval_to =on;" \
                   "select " \
                  "subdate('0002-1-1 23:59:59', interval 1 day to hour);" \
                  "select " \
                  "subdate('0002-1-1 23:59:59.9', interval 1 day to hour);" \
                  "select subdate" \
                  "('0002-1-1 23:59:59.9999999', interval 1 day to hour);" \
                  "select subdate" \
                  "('0002-1-1 23:59:59', interval 1.5 day to hour);" \
                  "select " \
                  "subdate('0002-1-1 23:59:59', interval 1.4 day to hour);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.assertTrue(len(sql_res.strip().splitlines()) >= 24, '行数不到24行')
        self.log.info(sql_res)
        self.assertEqual('0002-01-01 22:59:59',
                         sql_res.splitlines()[3].strip(), '执行失败' + text)
        self.assertEqual('0002-01-01 22:59:59.9',
                         sql_res.splitlines()[8].strip(), '执行失败' + text)
        self.assertEqual('0002-01-01 23:00:00',
                         sql_res.splitlines()[13].strip(), '执行失败' + text)
        self.assertEqual('0001-12-31 18:59:59',
                         sql_res.splitlines()[18].strip(), '执行失败' + text)
        self.assertEqual('0001-12-31 19:59:59',
                         sql_res.splitlines()[-2].strip(), '执行失败' + text)

        text = '--step9:interval的时间单位为day to second;expect:成功--'
        self.log.info(text)
        sql_cmd = "set dolphin.support_interval_to =on;" \
                   "select " \
                  "subdate('0002-1-1 23:59:59', interval 1 day to second);" \
                  "select " \
                  "subdate('0002-1-1 23:59:59.9', interval 1 day to second);" \
                  "select subdate" \
                  "('0002-1-1 23:59:59.9999999', interval 1 day to second);" \
                  "select subdate" \
                  "('0002-1-1 23:59:59.9999999', " \
                  "interval 1.9 day to second);" \
                  "select subdate" \
                  "('0002-1-1 23:59:59.9999999', interval 1.4 day to second);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.assertTrue(len(sql_res.strip().splitlines()) >= 24, '行数不到24行')
        self.log.info(sql_res)
        self.assertEqual('0002-01-01 23:59:58',
                         sql_res.splitlines()[3].strip(), '执行失败' + text)
        self.assertEqual('0002-01-01 23:59:58.9',
                         sql_res.splitlines()[8].strip(), '执行失败' + text)
        self.assertEqual('0002-01-01 23:59:59',
                         sql_res.splitlines()[13].strip(), '执行失败' + text)
        self.assertEqual('0002-01-01 23:58:51',
                         sql_res.splitlines()[18].strip(), '执行失败' + text)
        self.assertEqual('0002-01-01 23:58:56',
                         sql_res.splitlines()[-2].strip(), '执行失败' + text)

        text = '--step10:interval的时间单位为hour to minute;expect:成功--'
        self.log.info(text)
        sql_cmd = "set dolphin.support_interval_to =on;" \
                   "select " \
                  "subdate('0002-1-1 23:59:59', interval 1 hour to minute);" \
                  "select subdate" \
                  "('0002-1-1 23:59:59.9', interval 1 hour to minute);" \
                  "select subdate" \
                  "('0002-1-1 23:59:59.9999999', interval 1 hour to minute);" \
                  "select subdate" \
                  "('0002-1-1 23:59:59', interval 1.9 hour to minute);" \
                  "select subdate" \
                  "('0002-1-1 23:59:59', interval 1.4 hour to minute) ;"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.assertTrue(len(sql_res.strip().splitlines()) >= 24, '行数不到24行')
        self.log.info(sql_res)
        self.assertEqual('0002-01-01 23:58:59',
                         sql_res.splitlines()[3].strip(), '执行失败' + text)
        self.assertEqual('0002-01-01 23:58:59.9',
                         sql_res.splitlines()[8].strip(), '执行失败' + text)
        self.assertEqual('0002-01-01 23:59:00',
                         sql_res.splitlines()[13].strip(), '执行失败' + text)
        self.assertEqual('0002-01-01 22:50:59',
                         sql_res.splitlines()[18].strip(), '执行失败' + text)
        self.assertEqual('0002-01-01 22:55:59',
                         sql_res.splitlines()[-2].strip(), '执行失败' + text)

        text = '--step11:interval的时间单位为hour to second;expect:成功--'
        self.log.info(text)
        sql_cmd = "set dolphin.support_interval_to =on;" \
                   "select subdate" \
                  "('0002-1-1 23:59:59', interval 1 hour to second);" \
                  "select subdate" \
                  "('0002-1-1 23:59:59.9', INTERVAL 1 hour to second);" \
                  "select subdate" \
                  "('0002-1-1 23:59:59.9999999', INTERVAL 1 hour to second);" \
                  "select subdate" \
                  "('0002-1-1 23:59:59.9999999', " \
                  "INTERVAL 1.9 hour to second);" \
                  "select subdate('0002-1-1 23:59:59.9999999', " \
                  "INTERVAL 1.4 hour to second);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.assertTrue(len(sql_res.strip().splitlines()) >= 24, '行数不到24行')
        self.log.info(sql_res)
        self.assertEqual('0002-01-01 23:59:58',
                         sql_res.splitlines()[3].strip(), '执行失败' + text)
        self.assertEqual('0002-01-01 23:59:58.9',
                         sql_res.splitlines()[8].strip(), '执行失败' + text)
        self.assertEqual('0002-01-01 23:59:59',
                         sql_res.splitlines()[13].strip(), '执行失败' + text)
        self.assertEqual('0002-01-01 23:58:51',
                         sql_res.splitlines()[18].strip(), '执行失败' + text)
        self.assertEqual('0002-01-01 23:58:56',
                         sql_res.splitlines()[-2].strip(), '执行失败' + text)

        text = '--step12:interval的时间单位为minute to second;expect:成功--'
        self.log.info(text)
        sql_cmd = "set dolphin.support_interval_to =on;" \
                   "select subdate" \
                  "('0002-1-1 23:59:59', INTERVAL 1 minute to second);" \
                  "select subdate" \
                  "('0002-1-1 23:59:59.9', INTERVAL 1 minute to second);" \
                  "select subdate" \
                  "('0002-1-1 23:59:59.9999999', " \
                  "INTERVAL 1 minute to second);" \
                  "select subdate('0002-1-1 23:59:59.9', " \
                  "INTERVAL 1.9 minute to second);" \
                  "select subdate('0002-1-1 23:59:59.9', " \
                  "INTERVAL 1.4 minute to second);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.assertTrue(len(sql_res.strip().splitlines()) >= 24, '行数不到24行')
        self.log.info(sql_res)
        self.assertEqual('0002-01-01 23:59:58',
                         sql_res.splitlines()[3].strip(), '执行失败' + text)
        self.assertEqual('0002-01-01 23:59:58.9',
                         sql_res.splitlines()[8].strip(), '执行失败' + text)
        self.assertEqual('0002-01-01 23:59:59',
                         sql_res.splitlines()[13].strip(), '执行失败' + text)
        self.assertEqual('0002-01-01 23:58:50.9',
                         sql_res.splitlines()[18].strip(), '执行失败' + text)
        self.assertEqual('0002-01-01 23:58:55.9',
                         sql_res.splitlines()[-2].strip(), '执行失败' + text)

    def tearDown(self):
        text = '----step13:清理环境;expect:成功----'
        self.log.info(text)
        sql_cmd = f"drop database {self.db_name};"
        self.log.info(sql_cmd)
        drop_res1 = self.sh_primary.execut_db_sql(sql_cmd,
                                                  dbname='postgres')
        self.log.info(drop_res1)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, drop_res1,
                      '执行失败' + text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
